<?php

include "../lib/start.php";

check_session();
check_permission(ADM_PERM);
ini_set('display_errors', 1);

include "stat_create.html.php";
flush();

$sel_classi = "SELECT * FROM classi ";
if($_REQUEST['t'] == 1){
	$sel_classi .= "WHERE id_classe = ".$_REQUEST['classe'];
}
else if($_REQUEST['t'] == 2){
	$sel_classi .= "WHERE sezione = '".$_REQUEST['sezione']."'";
}
$sel_classi .= " ORDER BY sezione, anno_corso";
try{
	$res_classi = $db->executeQuery($sel_classi);
} catch(MySQLException $ex){
	$ex->alert();
}
  
$anno = substr($_SESSION['__current_year__']->get_data_chiusura(), 6);

$statistiche = array();
$db->select_db("information_schema");
$sel_suff = "SELECT table_name FROM tables WHERE table_name LIKE 'dati%' ORDER BY table_name DESC LIMIT 1";
$res_suff = $db->executeQuery($sel_suff);
$dt = $res_suff->fetch_assoc();
if($dt['table_name'] == "dati_".$anno."_2q"){
	// caso 1
	$case = 1;
	$suffix = $anno."_2q";
	$quadrimestre = "secondo";
}
else if($dt['table_name'] == "dati_".$anno."_1q"){
		// caso 1
	$case = 2;
	$suffix = $anno."_1q";
	$quadrimestre = "primo";
}
else if($dt['table_name'] == "dati_".($anno - 1)."_2q"){
	$case = 3;
	$suffix = ($anno - 1)."_2q";
	$quadrimestre = "secondo";
}
$db->select_db("registro_elettronico");

/*
 * ciclo su tutte le classi scelte
 * richiede una transazione
 */
$contatore_classi = 0;
$path = $_SERVER['DOCUMENT_ROOT']."/download/statistiche/$anno/";
if(!file_exists($path))
	mkdir($path, 0777, true);
while($classe = $res_classi->fetch_assoc()){
	$c_classe = $classe['anno_corso'].$classe['sezione'];
	print("<script>$('text_line').innerHTML = 'Classe ".$c_classe.": in corso...'</script>");
	flush();
	$filename = $c_classe."_".$classe['id_classe']."_".$anno.".xml";
	$doc = new DOMDocument('1.0');
	$doc->formatOutput = true;
	$doc->preserveWhiteSpace = false;
	
	if($quadrimestre == "primo"){
		/*
		 * statistica 1: numero alunni - vale per tutte le estrazioni
		 */
		$sel_alunni = "SELECT COUNT(*) AS val, sesso FROM alunni WHERE id_classe = ".$classe['id_classe']." GROUP BY sesso";
		try{
			$res_alunni = $db->executeQuery($sel_alunni);
		} catch(MySQLException $ex){
			$ex->alert();
		}
		$numero_alunni = array();
		while($a = $res_alunni->fetch_assoc()){
			$numero_alunni[$a['sesso']] = $a['val'];
			$numero_alunni['all'] += $a['val'];
		}
		$numero_alunni['case'] = "1,2,3";
		$numero_alunni['legend'] = "Numero alunni";
		$statistiche['alunni'] = $numero_alunni;
		/*
		 * creazione file xml: prima parte
		 */
		$doc->formatOutput = true;
		$root = $doc->createElement('Class');
		$root = $doc->appendChild($root);
		// classe
		$class = $doc->createElement('classe');
		$class = $root->appendChild($class);
		$class_text = $doc->createTextNode($classe['classe']);
		$class->appendChild($class_text);
		// sezione
		$sezione = $doc->createElement('sezione');
		$sezione = $root->appendChild($sezione);
		$sec_text = $doc->createTextNode($classe['sezione']);
		$sezione->appendChild($sec_text);
		// alunni totali
		$alunni = $doc->createElement('alunni');
		$alunni = $root->appendChild($alunni);
		$alunni_text = $doc->createTextNode($statistiche['alunni']['all']);
		$alunni->appendChild($alunni_text);
		// alunni femmine
		$alunni_f = $doc->createElement('femmine');
		$alunni_f = $root->appendChild($alunni_f);
		$alunnif_text = $doc->createTextNode($statistiche['alunni']['F']);
		$alunni_f->appendChild($alunnif_text);
		// alunni maschi
		$alunni_m = $doc->createElement('maschi');
		$alunni_m = $root->appendChild($alunni_m);
		$alunnim_text = $doc->createTextNode($statistiche['alunni']['M']);
		$alunni_m->appendChild($alunnim_text);
	}
	else{
		$doc->preserveWhiteSpace = false;
		$doc->load($path.$filename);
		$root = $doc->getElementsByTagName("Class")->item(0);
	}
	
	$statistiche_tag = $doc->createElement("statistiche");
	$statistiche_tag->setAttribute("quadrimestre", $quadrimestre);
	
	/*
	 * statistica n. 2: non validati ai sensi... - solo per case != 2
	 */
	if($case != 2){
		$sel_nv = "SELECT COUNT(*) AS count, sesso FROM non_classificati_".$suffix." WHERE id_classe = '".$classe['id_classe']."' GROUP BY sesso";
		try{
			$res_nv = $db->executeQuery($sel_nv);
		} catch(MySQLException $ex){
			$ex->alert();
		}
		$non_validati = array();
		$non_validati['case'] = "1,3";
		$non_validati['legend'] = "Alunni con anno non validato";
		$non_validati['all'] = $non_validati['F'] = $non_validati['M'] = 0;
		if($res_nv->num_rows > 0){
			while($v = $res_nv->fetch_assoc()){
				$non_validati[$v['sesso']] = $v['count'];
				$non_validati['all'] += $v['count'];
			}
		}
		$statistiche['non_validati'] = $non_validati;
		
		$statistica = $doc->createElement("statistica");
		$statistica->setAttribute("tipo", "generale");
		$statistica->setAttribute("legend", $non_validati['legend']);
		$dt_dato = $statistica->appendChild(new DomElement("dato"));
		$dt_dato->appendChild(new DOMText($non_validati['all']));
		$d_sesso = $statistica->appendChild(new DomElement("sesso"));
		$d_sesso->appendChild(new DOMText("all"));
		$d_order = $statistica->appendChild(new DomElement("order"));
		$d_order->appendChild(new DOMText("np"));
		$d4 = $statistica->appendChild(new DomElement("legend"));
		$d4->appendChild(new DOMText($non_validati['legend']));
		$statistiche_tag->appendChild($statistica);
		
		$statistica = $doc->createElement("statistica");
		$statistica->setAttribute("tipo", "generale");
		$statistica->setAttribute("legend", $non_validati['legend']);
		$dt_dato = $statistica->appendChild(new DomElement("dato"));
		$dt_dato->appendChild(new DOMText($non_validati['F']));
		$d_sesso = $statistica->appendChild(new DomElement("sesso"));
		$d_sesso->appendChild(new DOMText("F"));
		$d_order = $statistica->appendChild(new DomElement("order"));
		$d_order->appendChild(new DOMText("np"));
		$d4 = $statistica->appendChild(new DomElement("legend"));
		$d4->appendChild(new DOMText($non_validati['legend']));
		$statistiche_tag->appendChild($statistica);
		
		$statistica = $doc->createElement("statistica");
		$statistica->setAttribute("tipo", "generale");
		$statistica->setAttribute("legend", $non_validati['legend']);
		$dt_dato = $statistica->appendChild(new DomElement("dato"));
		$dt_dato->appendChild(new DOMText($non_validati['M']));
		$d_sesso = $statistica->appendChild(new DomElement("sesso"));
		$d_sesso->appendChild(new DOMText("M"));
		$d_order = $statistica->appendChild(new DomElement("order"));
		$d_order->appendChild(new DOMText("np"));
		$d4 = $statistica->appendChild(new DomElement("legend"));
		$d4->appendChild(new DOMText($non_validati['legend']));
		$statistiche_tag->appendChild($statistica);
	}
	/*
	 * statistica 3: non ammessi - $case != 2
	 * per case == 2 - non sufficienti
	 */
	$non_sufficienti = array();
	if($case == 2){
		$vista_in = "non_sufficienti";
		$non_sufficienti['case'] = "2";
		$non_sufficienti['legend'] = "Alunni con insufficienze";
	}
	else{
		$vista_in = "non_ammessi";
		$non_sufficienti['case'] = "1,3";
		$non_sufficienti['legend'] = "Alunni non ammessi";
	}
	$non_sufficienti['all'] = $non_sufficienti['M'] = $non_sufficienti['F'] = 0;
	$sel_na = "SELECT COUNT(*) AS count, sesso FROM ".$vista_in."_".$suffix." WHERE id_classe = '".$classe['id_classe']."' GROUP BY sesso";
	try{
		$res_na = $db->executeQuery($sel_na);
	} catch(MySQLException $ex){
		$ex->alert();
	}
	if($res_na->num_rows > 0){
		while($a = $res_na->fetch_assoc()){
			$non_sufficienti[$a['sesso']] = $a['count'];
			$non_sufficienti['all'] += $a['count'];
		}
	}
	$statistiche['non_sufficienti'] = $non_sufficienti;
	
	$statistica = $doc->createElement("statistica");
	$statistica->setAttribute("tipo", "generale");
	$statistica->setAttribute("legend", $non_sufficienti['legend']);
	$dt_dato = $statistica->appendChild(new DomElement("dato"));
	$dt_dato->appendChild(new DOMText($non_sufficienti['all']));
	$d_sesso = $statistica->appendChild(new DomElement("sesso"));
	$d_sesso->appendChild(new DOMText("all"));
	$d_order = $statistica->appendChild(new DomElement("order"));
	$d_order->appendChild(new DOMText("np"));
	$d4 = $statistica->appendChild(new DomElement("legend"));
	$d4->appendChild(new DOMText($non_sufficienti['legend']));
	$statistiche_tag->appendChild($statistica);

	$statistiche['non_sufficienti'] = $non_sufficienti;
	$statistica = $doc->createElement("statistica");
	$statistica->setAttribute("tipo", "generale");
	$statistica->setAttribute("legend", $non_sufficienti['legend']);
	$dt_dato = $statistica->appendChild(new DomElement("dato"));
	$dt_dato->appendChild(new DOMText($non_sufficienti['M']));
	$d_sesso = $statistica->appendChild(new DomElement("sesso"));
	$d_sesso->appendChild(new DOMText("M"));
	$d_order = $statistica->appendChild(new DomElement("order"));
	$d_order->appendChild(new DOMText("np"));
	$d4 = $statistica->appendChild(new DomElement("legend"));
	$d4->appendChild(new DOMText($non_sufficienti['legend']));
	$statistiche_tag->appendChild($statistica);

	$statistiche['non_sufficienti'] = $non_sufficienti;
	$statistica = $doc->createElement("statistica");
	$statistica->setAttribute("tipo", "generale");
	$statistica->setAttribute("legend", $non_sufficienti['legend']);
	$dt_dato = $statistica->appendChild(new DomElement("dato"));
	$dt_dato->appendChild(new DOMText($non_sufficienti['F']));
	$d_sesso = $statistica->appendChild(new DomElement("sesso"));
	$d_sesso->appendChild(new DOMText("F"));
	$d_order = $statistica->appendChild(new DomElement("order"));
	$d_order->appendChild(new DOMText("np"));
	$d4 = $statistica->appendChild(new DomElement("legend"));
	$d4->appendChild(new DOMText($non_sufficienti['legend']));
	$statistiche_tag->appendChild($statistica);
	
	/*
	 * statistica 4: media generale della classe - case = all
	 */
	$media_voto = array();
	$media_voto['case'] = "1,2,3";
	$media_voto['legend'] = "Media voto generale";
	$sel_media = "SELECT round, id_classe FROM classifica_classi_".$suffix." ORDER BY round DESC ";
	try{
		$res_media = $db->executeQuery($sel_media);
	} catch (MySQLException $ex){
		$ex->alert();
	}
	$count = 0;
	while($m = $res_media->fetch_assoc()){
		$count++;
		if($m['id_classe'] == $classe['id_classe']){
			$media_voto['all'] = $m['round'].";".$count;
			break;
		}		
	}
	$statistica = $doc->createElement("statistica");
	$statistica->setAttribute("tipo", "generale");
	$statistica->setAttribute("legend", $media_voto['legend']);
	$d1 = $statistica->appendChild(new DomElement("dato"));
	list($valore, $order) = explode(";", $media_voto['all']);
	$d1->appendChild(new DOMText($valore));
	$d2 = $statistica->appendChild(new DomElement("sesso"));
	$d2->appendChild(new DOMText("all"));
	$d3 = $statistica->appendChild(new DomElement("order"));
	$d3->appendChild(new DOMText($order));
	$d4 = $statistica->appendChild(new DomElement("legend"));
	$d4->appendChild(new DOMText($media_voto['legend']));
	$statistiche_tag->appendChild($statistica);
	
	$sel_media_m = "SELECT round, id_classe FROM classifica_classi_sesso_".$suffix." WHERE sesso = 'M' ORDER BY round DESC ";
	try{
		$res_media_m = $db->executeQuery($sel_media_m);
	} catch (MySQLException $ex){
		$ex->alert();
	}
	$count = 0;
	while($m = $res_media_m->fetch_assoc()){
		$count++;
		if($m['id_classe'] == $classe['id_classe']){
			$media_voto['M'] = $m['round'].";".$count;
			break;
		}	
	}
	$statistica = $doc->createElement("statistica");
	$statistica->setAttribute("tipo", "generale");
	$statistica->setAttribute("legend", $media_voto['legend']);
	$d1 = $statistica->appendChild(new DomElement("dato"));
	list($valore, $order) = explode(";", $media_voto['M']);
	$d1->appendChild(new DOMText($valore));
	$d2 = $statistica->appendChild(new DomElement("sesso"));
	$d2->appendChild(new DOMText("M"));
	$d3 = $statistica->appendChild(new DomElement("order"));
	$d3->appendChild(new DOMText($order));
	$d4 = $statistica->appendChild(new DomElement("legend"));
	$d4->appendChild(new DOMText($media_voto['legend']));
	$statistiche_tag->appendChild($statistica);
	
	$sel_media_f = "SELECT round, id_classe FROM classifica_classi_sesso_".$suffix." WHERE sesso = 'F' ORDER BY round DESC ";
	try{
		$res_media_f = $db->executeQuery($sel_media_f);
	} catch(MySQLException $ex){
		$ex->alert();
	}
	$count = 0;
	while($f = $res_media_f->fetch_assoc()){
		$count++;
		if($f['id_classe'] == $classe['id_classe']){
			$media_voto['F'] = $f['round'].";".$count;
			break;
		}	
	}
	$statistica = $doc->createElement("statistica");
	$statistica->setAttribute("tipo", "generale");
	$statistica->setAttribute("legend", $media_voto['legend']);
	$d1 = $statistica->appendChild(new DomElement("dato"));
	list($valore, $order) = explode(";", $media_voto['F']);
	$d1->appendChild(new DOMText($valore));
	$d2 = $statistica->appendChild(new DomElement("sesso"));
	$d2->appendChild(new DOMText("F"));
	$d3 = $statistica->appendChild(new DomElement("order"));
	$d3->appendChild(new DOMText($order));
	$d4 = $statistica->appendChild(new DomElement("legend"));
	$d4->appendChild(new DOMText($media_voto['legend']));
	$statistiche_tag->appendChild($statistica);

	$statistiche['media_voto'] = $media_voto;
	
	/*
	 * medie per materia
	 */
	$sel_materie = "SELECT * FROM materie WHERE has_sons = 0 AND id_materia < 20 AND id_materia != 11 AND id_materia != 10";
	try{
		$res_materie = $db->executeQuery($sel_materie);
	} catch (MySQLException $ex){
		$ex->alert();
	}
	while($materia = $res_materie->fetch_assoc()){
		$mat = strtolower($materia['materia']);
		$mat = preg_replace("/ /", "_", $mat);
		$$mat = array();
		$$mat['case'] = "1,2,3";
		$$mat['legend'] = "Media voto $mat";
		/*
		 * query media generale
		 */
		$sel_mat = "SELECT id_classe, round FROM classifica_".$mat."_".$suffix." ORDER BY round DESC";
		try{
			$res_mat = $db->executeQuery($sel_mat);
		} catch (MySQLException $ex){
			$ex->alert();
		}
		$indice = 0;
		while($c = $res_mat->fetch_assoc()){
			$indice++;
			if($classe['id_classe'] == $c['id_classe'])
				$$mat['all'] = $c['round'].";".$indice;
		}
		$statistica = $doc->createElement("statistica");
		$statistica->setAttribute("tipo", $mat);
		$d1 = $statistica->appendChild(new DomElement("dato"));
		list($valore, $order) = explode(";", $$mat['all']);
		$d1->appendChild(new DOMText($valore));
		$d2 = $statistica->appendChild(new DomElement("sesso"));
		$d2->appendChild(new DOMText("all"));
		$d3 = $statistica->appendChild(new DomElement("order"));
		$d3->appendChild(new DOMText($order));
		$d4 = $statistica->appendChild(new DomElement("legend"));
		$d4->appendChild(new DOMText($$mat['legend']));
		$statistiche_tag->appendChild($statistica);
		
		/*
		 * query media per sesso
		 */
		$indice_m = 0;
		$sel_s_mat = "SELECT id_classe, round FROM classifica_".$mat."_sesso_".$suffix." WHERE sesso = 'M' ORDER BY round DESC";
		try{
			$res_s_mat = $db->executeQuery($sel_s_mat);
		} catch (MySQLException $ex){
			$ex->alert();
		}
		while($c_m = $res_s_mat->fetch_assoc()){
			$indice_m++;
			if($classe['id_classe'] == $c_m['id_classe'])
				$$mat['M'] = $c_m['round'].";".$indice_m;
		}
		$statistica = $doc->createElement("statistica");
		$statistica->setAttribute("tipo", $mat);
		$d1 = $statistica->appendChild(new DomElement("dato"));
		list($valore, $order) = explode(";", $$mat['M']);
		$d1->appendChild(new DOMText($valore));
		$d2 = $statistica->appendChild(new DomElement("sesso"));
		$d2->appendChild(new DOMText("M"));
		$d3 = $statistica->appendChild(new DomElement("order"));
		$d3->appendChild(new DOMText($order));
		$d4 = $statistica->appendChild(new DomElement("legend"));
		$d4->appendChild(new DOMText($$mat['legend']));
		$statistiche_tag->appendChild($statistica);
		
		$indice_f = 0;
		$sel_f_mat = "SELECT id_classe, round FROM classifica_".$mat."_sesso_".$suffix." WHERE sesso = 'F' ORDER BY round DESC";
		try{
			$res_f_mat = $db->executeQuery($sel_f_mat);
		} catch (MySQLException $ex){
			$ex->alert();
		}
		while($c_f = $res_f_mat->fetch_assoc()){
			$indice_f++;
			if($classe['id_classe'] == $c_f['id_classe'])
				$$mat['F'] = $c_f['round'].";".$indice_f;
		}
		$statistica = $doc->createElement("statistica");
		$statistica->setAttribute("tipo", "$mat");
		$d1 = $statistica->appendChild(new DomElement("dato"));
		list($valore, $order) = explode(";", $$mat['F']);
		$d1->appendChild(new DOMText($valore));
		$d2 = $statistica->appendChild(new DomElement("sesso"));
		$d2->appendChild(new DOMText("F"));
		$d3 = $statistica->appendChild(new DomElement("order"));
		$d3->appendChild(new DOMText($order));
		$d4 = $statistica->appendChild(new DomElement("legend"));
		$d4->appendChild(new DOMText($$mat['legend']));
		$statistiche_tag->appendChild($statistica);
		$statistiche[$mat] = $$mat;
	}
	
	$root->appendChild($statistiche_tag);
	$doc->save($path.$filename);
	$contatore_classi++;
	print("<script>$('text_line').innerHTML = 'Classe ".$c_classe.": completata'</script>");
	flush();
}
print("<script>$('text_line').innerHTML = 'Operazione completata'</script>");
?>